package fina2.xls;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.TreeMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.naming.InitialContext;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import fina2.dcs.context.ContextHelper;
import fina2.dcs.system.SysProperty;
import fina2.dcs.system.SysPropertyLocal;

/**
 * 
 * @author datojava Instead of fina2.xls.XLSValidator please use
 *         fina2.dcs.common.xls.XLSValidator
 */
@Deprecated
public class XLSValidator {

	private static Logger log = Logger.getLogger(XLSValidator.class);

	private static ArrayList<String> unprotectedSheets = new ArrayList<String>();

	private static ArrayList<String> nopasswordSheets = new ArrayList<String>();

	public static ArrayList<String> getUnprotectedSheets() {
		return unprotectedSheets;
	}

	public static ArrayList<String> getNopasswordSheets() {
		return nopasswordSheets;
	}

	private XLSValidator() {

	}

	@SuppressWarnings("unused")
	public static boolean isXLSFile(File file) {

		boolean isXLS = true;
		try {
			FileInputStream fis = new FileInputStream(file);
			POIFSFileSystem s = new POIFSFileSystem(fis);
			HSSFWorkbook wb = new HSSFWorkbook(s);
		} catch (OfficeXmlFileException ex) {
			isXLS = false;
			log.info(ex.getMessage());
		} catch (IOException ex) {
			isXLS = false;
			log.info(ex.getMessage());
		} catch (Exception ex) {
			isXLS = false;
			ex.printStackTrace();
		}
		return isXLS;
	}

	public static String getMatrixByName(String xlsName) {
		String mainMat = "C:\\fina-server\\Matrix.xls";
		String matrix = "C:\\fina-server\\";
		try {
			InitialContext ic = ContextHelper.getInitialContext();
			SysPropertyLocal spl = ContextHelper.injectSysPropLocal(ic);
			SysProperty spDefault = spl.findProperty("fina2.mfb.xls.name.pattern");
			SysProperty spMfbHis = spl.findProperty("fina2.mfb.xls.historic.name.pattern");
			XLSReader xlsReader = new XLSReader(new File(mainMat));
			HSSFWorkbook matWb = new HSSFWorkbook(new FileInputStream(new File(mainMat)));
			if (isXLSNameValidBy(xlsName, spDefault.getValue().trim().toLowerCase())) {
				matrix += xlsReader.getValueByRowCol(matWb, "Options", 0, 1).toString().trim();
			} else if (isXLSNameValidBy(xlsName, spMfbHis.getValue().trim().toLowerCase())) {
				matrix += xlsReader.getValueByRowCol(matWb, "Options", 1, 1).toString().trim();
			}

		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
		return matrix;
	}

	public static boolean isXLSNameValidBy(String name, String pattern) {
		Pattern p = Pattern.compile(pattern.toLowerCase());
		Matcher m = p.matcher(name.toLowerCase());
		return m.matches();
	}

	public static boolean isXLSNameValid(String name) {
		String regEx = null;

		boolean isValid = false;

		try {
			InitialContext ic = ContextHelper.getInitialContext();
			SysPropertyLocal spl = ContextHelper.injectSysPropLocal(ic);
			SysProperty spDefault = null;
			SysProperty spHistoric = null;

			spDefault = spl.findProperty("fina2.mfb.xls.name.pattern");
			spHistoric = spl.findProperty("fina2.mfb.xls.historic.name.pattern");

			regEx = spDefault.getValue().toLowerCase();
			Pattern pattern = Pattern.compile(regEx);
			Matcher matches = pattern.matcher(name.toLowerCase());
			if (matches.matches()) {
				isValid = true;
			} else {
				regEx = spHistoric.getValue().toLowerCase();
				pattern = Pattern.compile(regEx);
				matches = pattern.matcher(name.toLowerCase());
				if (matches.matches()) {
					isValid = true;
				}
			}
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
		return isValid;
	}

	public static boolean isXLSProtectionValidByOptions(File xls, String matrixPath) {

		boolean isProtectedByOpt = true;
		try {
			File matrix = new File(matrixPath);

			XLSReader xlsReader = new XLSReader(matrix);
			TreeMap<String, Boolean> options = xlsReader.loadProtectionProperties();
			if (options.size() > 0) {
				FileInputStream fis = new FileInputStream(xls);
				POIFSFileSystem s = new POIFSFileSystem(fis);
				HSSFWorkbook workbook = new HSSFWorkbook(s);

				Iterator<String> iter = options.keySet().iterator();
				unprotectedSheets.clear();
				while (iter.hasNext()) {
					String key = iter.next();
					HSSFSheet sheet = workbook.getSheet(key);
					if (options.get(key) && (!sheet.getProtect())) {
						isProtectedByOpt = false;
						unprotectedSheets.add(sheet.getSheetName());
					}
				}
			}

		} catch (Exception ex) {
			isProtectedByOpt = false;
			// ex.printStackTrace();
		}

		return isProtectedByOpt;
	}

	public static boolean isXLSProtectionPasswordValid(File xls, String matrixPath) {
		boolean passwordValid = true;
		try {
			InitialContext ic = ContextHelper.getInitialContext();
			SysPropertyLocal spl = ContextHelper.injectSysPropLocal(ic);
			SysProperty sp = spl.findProperty("fina2.sheet.protection.password");
			String password = sp.getValue();

			File matrix = new File(matrixPath);
			XLSReader xlsReader = new XLSReader(matrix);
			TreeMap<String, Boolean> options = xlsReader.loadProtectionProperties();
			if (options.size() > 0) {
				FileInputStream fis = new FileInputStream(xls);
				POIFSFileSystem s = new POIFSFileSystem(fis);
				HSSFWorkbook workbook = new HSSFWorkbook(s);

				nopasswordSheets.clear();

				Iterator<String> iter = options.keySet().iterator();
				while (iter.hasNext()) {
					String key = iter.next();
					HSSFSheet sheet = workbook.getSheet(key);
					if (options.get(key) && (!((Short) sheet.getPassword()).toString().equals(password))) {
						passwordValid = false;
						nopasswordSheets.add(sheet.getSheetName());
					}
				}
			}

			/*
			 * for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
			 * HSSFSheet sheet = workbook.getSheetAt(i); if (!((Short)
			 * sheet.getPassword()).toString().equals(password)) { passwordValid
			 * = false; break; } }
			 */
		} catch (Exception ex) {
			passwordValid = false;
		}
		return passwordValid;
	}
}